<?php

include_once GMANAGER_LIBS.'/excel_data_manager/libs/PHPExcel.php';

/**
 *  ExcelDataManager 只是操作 PHPExcel 來取得少部份所需要功能
 *  如果要操作 excel 請直接使用 PHPExcel library
 *
 *  注意:
 *      使用本程式必須先引用 PHPExcel library
 *
 */
class ExcelDataManager
{
    private $_excel;

    /**
     *  讀取 excel file
     *  重設 sheet index
     */
    public function setFile($file) 
    {
        $this->_excel = PHPExcel_IOFactory::load($file);
        $this->_excel->setActiveSheetIndex(0);
    }

    /**
     *  設定 sheet index
     *  @return boolean  -  true=設定成功 / false=設定失敗,index重新設為0
     */
    private function _setIndex($index=0) 
    {
        $sheetCount = $this->_excel->getSheetCount();
        if( $index >= $sheetCount ) {
            $this->_excel->setActiveSheetIndex(0);
            return false;
        }
        $this->_excel->setActiveSheetIndex($index);
        return true;
    }

    /**
     *  get all row array by sheet 
     *
     *  @return boolean
     */
    public function getAllRowsArray() {
        
        $activeSheetIndex   = $this->_excel->getActiveSheetIndex();
        $sheetCount         = $this->_excel->getSheetCount();
        $data               = Array();
        
        for( $index=0; $index<$sheetCount; $index++ ) {
            $data[] = $this->getRowsArray($index);
        }

        $this->_excel->setActiveSheetIndex( $activeSheetIndex );
        return $data;

    }

    /**
     *  查看簡易資訊
     */
    public function getInfo()
    {
        $data = Array();
        if( $this->_excel ) {
            $data += Array(
                'active_sheet_index' => $this->_excel->getActiveSheetIndex(),
                'sheet_count'        => $this->_excel->getSheetCount(),
                'sheet_names'        => $this->_excel->getSheetNames(),
              //'named_ranges'       => $this->_excel->getNamedRanges(),
              //'security'           => $this->_excel->getSecurity(),
              //'properties'         => $this->_excel->getProperties(),
            );
            $data += Array(
                'sheet_title'       => $this->_excel->getActiveSheet()->getTitle(),
                'highest_column'    => $this->_excel->getActiveSheet()->getHighestColumn(),
                'highest_row'       => $this->_excel->getActiveSheet()->getHighestRow(),
            );
        }
        return $data;
    }

    /**
     *  注意:
     *      該程式的目的是取得 excel 中有資料的 row data , 當 一行row 中沒有任何資料時, 將不儲存至陣列
     *  
     *  @param  index - 取得那個 index 的 sheet
     *  @return Array
     */
    public function getRowsArray( $index=0 )
    {
        if( !$this->_setIndex($index) ) {
            return Array();
        }
        
        $data = Array();
        $columnMax = $this->_excel->getActiveSheet()->getHighestColumn();
        $rowMax    = $this->_excel->getActiveSheet()->getHighestRow();
        for( $i=1; $i<=$rowMax; $i++ ) {
            for( $j='A'; $j<=$columnMax; $j++ ) {
                $data[$i][$j] = trim($this->_excel->getActiveSheet()->getCell($j.$i)->getValue());
           }
        }

        // 所有的 空行column 都移除
        foreach( $data as $key=>$line ) {
            $theRowIsEmpty = true;
            foreach( $line as $value ) {
                if( $value ) {
                    $theRowIsEmpty = false;
                    break;
                }
            }
            if( $theRowIsEmpty ) {
                unset($data[$key]);
            }
        }

        // 檢查陣列第二維的索引數量是否正確
        // 不正確將傳出空的 array
        $prevCount = -1;
        $nowCount  = -1;
        foreach( $data as $key=>$item ) {
            $nowCount = count($item);
            if( $prevCount != -1 && $nowCount != $prevCount ) {
                return Array();
            }
            $prevCount = $nowCount;
            $nowCount = -1;
        }

        // 重新設定第一維的陣列索引
        $data = array_values($data);

        // 重新設定第二維的陣列索引
        foreach( $data as $key=>$item ) {
            $data[$key] = array_values($item);
        }

        return $data;
    }

    /**
     *  注意:
     *      該程式的目的是取得 excel 中有資料的 column data , 當 一行column 中沒有任何資料時, 將不儲存至陣列
     *   
     *  @param  index - 取得那個 index 的 sheet
     *  @return Array
     */
    public function getColumnsArray( $index=0 )
    {
        if( !$this->_setIndex($index) ) {
            return Array();
        }

        $data = Array();
        $columnMax = $this->_excel->getActiveSheet()->getHighestColumn();
        $rowMax    = $this->_excel->getActiveSheet()->getHighestRow();
        for( $i='A'; $i<=$columnMax; $i++ ) {
            for( $j=1; $j<=$rowMax; $j++ ) {
                $data[$i][$j] = trim($this->_excel->getActiveSheet()->getCell($i.$j)->getValue());
           }
        }

        // 所有的 空行column 都移除
        for( $i=0; $i<=$rowMax; $i++ ) {
            $theColumnIsEmpty = true;
            for( $j='A'; $j<=$columnMax; $j++  ) {
                if( $data[$j][$i] ) {
                    $theColumnIsEmpty = false;
                    break;
                }
            }
            if( true===$theColumnIsEmpty ) {
                for( $j='A'; $j<=$columnMax; $j++  ) {
                    unset( $data[$j][$i] );
                }
            }
        }

        // 檢查陣列第二維的索引數量是否正確
        // 不正確將傳出空的 array
        $prevCount = -1;
        $nowCount  = -1;
        foreach( $data as $key=>$item ) {
            $nowCount = count($item);
            if( $prevCount != -1 && $nowCount != $prevCount ) {
                return Array();
            }
            $prevCount = $nowCount;
            $nowCount = -1;
        }

        // 重新設定第一維的陣列索引
        $data = array_values($data);

        // 重新設定第二維的陣列索引
        foreach( $data as $key=>$item ) {
            $data[$key] = array_values($item);
        }

        return $data;
    }

    /**
     *  get sheet table format html
     */
    function getSheetTableHtml( $index=0 )
    {
        if( !$this->_setIndex($index) ) {
            return Array();
        }

        $rows     = $this->getRowsArray();
        $title    = $this->_excel->getActiveSheet()->getTitle();
        $rowCount = count($rows[0]);
        $table    = '<tr><td colspan="'. $rowCount .'" style="text-align:left;font-weight:bold;" >['. $title .']</td></tr>';
        foreach( $rows as $key => $line ) {
            $table .= '<tr>';
            foreach( $line as $item ) {
                $table .= "<td>{$item}</td>";
            }
            $table .= '</tr>';
        }
        return <<<EOD
            <table cellpadding="0" cellspacing="0" style="width:800px;border: 1px solid gray; word-break:break-all; word-wrap:break-word; table-layout:fixed;">
                <tbody>
                    {$table}
                </tbody>
            </table>
EOD;

    }

}

